/********************
* population conversion to sa2 2016 from sa2 2011
*******************/

/* convert to dataframe */
proc import out = codes.sa2_cor datafile = 'C:\Users\kcha0642\Documents\NSW-ON-NY\on-ny-nsw-kcha0642\on-ny-nsw\sa2_correspondance' 
	dbms = xlsx replace; 
	getnames = yes; 
run; 

/********************
* macro sa2 conversion
* input: cohort table
			level = ppn or recnum (persons or episode id)
* output: table by counts of episodes/persons in each SA2 2016 based on SA2 2011 level
*******************/
%macro sa2_conversion(cohort,output,level); 

proc sql; 
	create table temp as
	select a.SA2_2011_CODE, count(distinct a.&level) as n_levels 
	from &cohort as a
	group by a.SA2_2011_CODE; 

proc sql; 
	create table temp_cohort as
	select a.n_levels, a.SA2_2011_CODE as sa2_2011, b.sa2_maincode_2016 as sa2_2016, b.sa2_name_2016 as sa2_2016_name, b.ratio
	from temp as a
	left join codes.sa2_cor as b
	on a.SA2_2011_CODE = b.sa2_maincode_2011; 

data temp_cohort; 
	set temp_cohort; 
	converted = n_levels * ratio; 
run; 

proc sql; 
	create table &output as
	select a.sa2_2016, a.sa2_2016_name, sum(converted) as n_levels
	from temp_cohort as a
	group by a.sa2_2016, a.sa2_2016_name; 

%mend sa2_conversion;
